package com.ssyt.ec.exam.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.ssyt.ec.common.PageModel;
import com.ssyt.ec.common.util.DBUtil;
import com.ssyt.ec.exam.entity.ExamPaperEntity;
import com.ssyt.ec.exam.entity.ExamPlanEntity;
import com.ssyt.ec.stu.entity.ClassEntity;
import com.ssyt.ec.stu.entity.UserEntity;

/**
 * @author lwj
 *
 *         2016年11月19日
 */
public class ExamPlanService {
	/**
	 * 考试安排查询
	 * 
	 * @param userId
	 * @param expire
	 * @param state
	 * @param type
	 * @param order
	 * @param sort
	 * @param rows
	 * @param page
	 * 
	 * @return
	 */
	public PageModel<ExamPlanEntity> listExamPlan(Map<Object, Object> paramMap) {
		List<Object> listParam = new ArrayList<>();
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT pl.id, ");
		sb.append("pl.exam_time_start, ");
		sb.append("pl.exam_time_stop, ");
		sb.append("pl.exam_classroom, ");
		sb.append("pa.id AS exam__paper__id, ");
		sb.append("pa.name AS exam__paper__name, ");
		sb.append("pl.to_user_ids, ");
		sb.append("pl.to_class_id, ");
		sb.append("pl.operate_user_id, ");
		sb.append("pl.operate_time, ");
		sb.append("pl.state,");
		sb.append("pl.memo ");
		sb.append("FROM exam_plan pl,qes_exam_paper pa ");
		sb.append("WHERE pl.exam_paper_id=pa.id ");
		if (StringUtils.isNotBlank((String) paramMap.get("state"))) {
			sb.append("AND pl.state=? ");
			listParam.add(paramMap.get("state"));
		} else {
			sb.append("AND pl.state NOT IN(3) ");
		}
		if ("1".equals(paramMap.get("type"))) {
			sb.append("AND pl.operate_user_id=? ");
			listParam.add(paramMap.get("userId"));
		} else {
			sb.append("AND pl.operate_user_id NOT IN(?) ");
			listParam.add(paramMap.get("userId"));
		}
		if (StringUtils.isNotBlank((String) paramMap.get("expire"))) {
			if ("1".equals(paramMap.get("expire"))) {
				sb.append("AND pl.exam_time_stop>NOW() ");
			} else {
				sb.append("AND pl.exam_time_stop<NOW() ");
			}
		}
		sb.append("ORDER BY ");
		sb.append(paramMap.get("sort") + " " + paramMap.get("order"));
		sb.append(" LIMIT ?,?");
		int page = (int) paramMap.get("page");
		int rows = (int) paramMap.get("rows");
		listParam.add((page - 1) * rows);
		listParam.add(rows);
		return DBUtil.executeQueryByPage(ExamPlanEntity.class, sb.toString(),
				listParam);
	}

	/**
	 * 查询所有学生用户
	 * 
	 * @return
	 */
	public List<UserEntity> listUser() {
		String sql = "SELECT * FROM sys_user WHERE role_id=3";
		return DBUtil.executeQuery(UserEntity.class, sql);
	}

	/**
	 * 试卷查询
	 * 
	 * @return
	 */
	public List<ExamPaperEntity> listPaper() {
		String sql = "SELECT id,name FROM qes_exam_paper";
		return DBUtil.executeQuery(ExamPaperEntity.class, sql);
	}

	/**
	 * 班级查询
	 * 
	 * @return
	 */
	public List<ClassEntity> listClass() {
		String sql = "SELECT id,name FROM sys_class";
		return DBUtil.executeQuery(ClassEntity.class, sql);
	}

	/**
	 * 新增考试安排
	 * 
	 * @param paramList
	 * @return
	 */
	public long add(List<Object> paramList) {
		Connection connection = DBUtil.getConnection();
		String sql = "INSERT INTO exam_plan VALUES(NULL,?,?,?,?,?,?,?,NOW(),?,?)";
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = connection.prepareStatement(sql,
					Statement.RETURN_GENERATED_KEYS);
			DBUtil.setParameter(paramList, preparedStatement);
			preparedStatement.executeUpdate();
			ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
			if (generatedKeys.next()) {
				return generatedKeys.getLong(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(preparedStatement, connection);
		}
		return 0;
	}

	/**
	 * 按id查询考试安排
	 * 
	 * @param id
	 * @return
	 */
	public ExamPlanEntity getExamPlan(String id) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT pl.id, ");
		sb.append("pl.exam_time_start, ");
		sb.append("pl.exam_time_stop, ");
		sb.append("pl.exam_classroom, ");
		sb.append("pa.id AS exam__paper__id, ");
		sb.append("pa.name AS exam__paper__name, ");
		sb.append("pl.to_user_ids, ");
		sb.append("pl.to_class_id, ");
		sb.append("pl.operate_user_id, ");
		sb.append("pl.operate_time, ");
		sb.append("pl.state,");
		sb.append("pl.memo ");
		sb.append("FROM exam_plan pl,qes_exam_paper pa ");
		sb.append("WHERE pl.exam_paper_id=pa.id AND pl.id=?");
		return DBUtil.getUniqueResult(ExamPlanEntity.class, sb.toString(), id);
	}

	/**
	 * 修改
	 * 
	 * @param paramList
	 * @return
	 */
	public int update(List<Object> paramList) {
		StringBuffer sb = new StringBuffer();
		sb.append("UPDATE exam_plan SET");
		sb.append("exam_time_start=?, ");
		sb.append("exam_time_stop=?, ");
		sb.append("exam_classroom=?, ");
		sb.append("exam_paper_id=?, ");
		sb.append("to_user_ids=?, ");
		sb.append("to_class_id=?, ");
		sb.append("operate_user_id=?, ");
		sb.append("operate_time=?, ");
		sb.append("state=?,");
		sb.append("memo=? ");
		sb.append("WHERE id=?");
		return DBUtil.executeUpdate(sb.toString(), paramList);
	}

	/**
	 * 删除
	 * 
	 * @param ids
	 * @param userId
	 * @return
	 */
	public int delete(String[] ids, int userId) {
		String sql = "UPDATE exam_plan SET state=3,operate_user_id=?,operate_time=NOW() WHERE id IN ("
				+ StringUtils.join(ids) + ")";
		return DBUtil.executeUpdate(sql, userId);
	}

	/**
	 * 启用
	 * 
	 * @param ids
	 * @param userId
	 * @return
	 */
	public int open(String[] ids, int userId) {
		String sql = "UPDATE exam_plan SET state=1,operate_user_id=?,operate_time=NOW() WHERE id IN ("
				+ StringUtils.join(ids) + ")";
		return DBUtil.executeUpdate(sql, userId);
	}

	/**
	 * 获取paper地址
	 * 
	 * @param examPlanId
	 */
	public String getPaperUrl(long examPlanId) {
		String sql = "SELECT pa.paper_url FROM exam_plan pl,qes_exam_paper pa WHERE pl.exam_paper_id=pa.id AND pl.id=?";
		ExamPaperEntity examPaperEntity = DBUtil.getUniqueResult(
				ExamPaperEntity.class, sql, examPlanId);
		return examPaperEntity.getPaperUrl();
	}

}
